{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 聚宽平台下载数据并导出CSV格式"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "  * 引入模块"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import time\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from sqlalchemy import *"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "  * 从平台获取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据总shape为(72521, 238)\n",
      "平台获取数据成功，用时104.82502961158752\n",
      "平台导出数据成功，用时21.179389476776123\n"
     ]
    }
   ],
   "source": [
    "# 设置初始时间\n",
    "t0 = time.time()\n",
    "# 获取2015年第一季度初始数据\n",
    "financial_data = get_fundamentals(query(\n",
    "    valuation, balance, cash_flow, income, indicator), statDate='2015q1')\n",
    "# 设置时间区间\n",
    "time_range = ['2015q2', '2015q3', '2015q4', '2016q1', '2016q2', '2016q3', '2016q4', '2017q1', '2017q2',\n",
    "              '2017q3', '2017q4', '2018q1', '2018q2', '2018q3', '2018q4', '2019q1', '2019q2', '2019q3',\n",
    "              '2019q4', '2020q1', '2020q2']\n",
    "# 遍历每个时间点获取季度数据\n",
    "for i in time_range:\n",
    "    financial_per = get_fundamentals(query(\n",
    "        valuation, balance, cash_flow, income, indicator), statDate=i)\n",
    "    financial_data = financial_data.append(financial_per, ignore_index=True)\n",
    "\n",
    "print('数据总shape为{}'.format(financial_data.shape))\n",
    "# 设置从平台获取数据结束时间\n",
    "t1 = time.time()\n",
    "print('平台获取数据成功，用时{}'.format(t1 - t0))\n",
    "# 将数据导出为csv格式\n",
    "financial_data.to_csv('financial_data.csv')\n",
    "# 设置导出csv格式结束时间\n",
    "t2 = time.time()\n",
    "print('平台导出数据成功，用时{}'.format(t2 - t1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 本地平台操作，导入本地数据库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "创建表格...\n",
      "表格创建成功, 用时0.2秒\n",
      "数据导入本地数据库中...\n",
      "提取数据完成，用时3.3秒\n",
      "code表数据导入成功, 用时0.2秒\n",
      "valuation表数据导入成功, 用时3.9秒\n",
      "balance表数据导入成功, 用时19.7秒\n",
      "cash_flow表数据导入成功, 用时14.2秒\n",
      "income表数据导入成功, 用时10.3秒\n",
      "indicator表数据导入成功, 用时9.1秒\n",
      "数据导入本地数据库完成，总用时60.6秒\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import time\n",
    "import pymysql\n",
    "import warnings\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "\n",
    "pd.set_option('display.max_columns', 60)\n",
    "d = os.path.dirname('.')\n",
    "run_dir = os.path.abspath(d)\n",
    "os.chdir(run_dir)\n",
    "warnings.filterwarnings('ignore')  # 忽视警告\n",
    "\n",
    "\n",
    "def connect_method(method='python'):\n",
    "    if method == 'python':\n",
    "        conn = pymysql.Connect(\n",
    "            host='localhost',\n",
    "            port=3306,\n",
    "            user='root',\n",
    "            password='19930331zy',\n",
    "            database='financial',\n",
    "            charset='utf8'\n",
    "        )\n",
    "        return conn\n",
    "    elif method == 'pandas':\n",
    "        conn = create_engine('mysql+pymysql://root:19930331zy@localhost:3306/financial?charset=utf8')\n",
    "        return conn\n",
    "    else:\n",
    "        raise TypeError('method无效')\n",
    "\n",
    "\n",
    "def creat_table(conn):\n",
    "    # 创表语句\n",
    "    t0 = time.time()\n",
    "    sql_code = 'create table code(' \\\n",
    "               'id int primary key auto_increment,' \\\n",
    "               'code varchar(15) not null unique);'\n",
    "    sql_valuation = 'create table valuation(' \\\n",
    "                    'id int primary key auto_increment,' \\\n",
    "                    'code_id int,' \\\n",
    "                    'day date ,' \\\n",
    "                    'capitalization double ,' \\\n",
    "                    'market_cap double ,' \\\n",
    "                    'circulating_cap double ,' \\\n",
    "                    'circulating_market_cap double ,' \\\n",
    "                    'turnover_ratio float ,' \\\n",
    "                    'pe_ratio float ,' \\\n",
    "                    'pe_ratio_lyr float ,'\\\n",
    "                    'pb_ratio float ,' \\\n",
    "                    'ps_ratio float ,' \\\n",
    "                    'pcf_ratio float ,' \\\n",
    "                    'constraint c1 foreign key (code_id) references code(id) on delete cascade );'\n",
    "    sql_balance = 'create table balance(' \\\n",
    "                  'id int primary key auto_increment,' \\\n",
    "                  'code_id int,' \\\n",
    "                  'pubdate date ,' \\\n",
    "                  'statdate date ,'\\\n",
    "                  'cash_equivalents float,' \\\n",
    "                  'settlement_provi float,' \\\n",
    "                  'lend_capital float,' \\\n",
    "                  'trading_assets float,' \\\n",
    "                  'bill_receivable float,' \\\n",
    "                  'account_receivable float,' \\\n",
    "                  'advance_payment float,' \\\n",
    "                  'insurance_receivables float,' \\\n",
    "                  'reinsurance_receivables float,' \\\n",
    "                  'reinsurance_contract_reserves_receivable float,' \\\n",
    "                  'interest_receivable float,' \\\n",
    "                  'dividend_receivable float,' \\\n",
    "                  'other_receivable float,' \\\n",
    "                  'bought_sellback_assets float,' \\\n",
    "                  'inventories float,' \\\n",
    "                  'non_current_asset_in_one_year float,' \\\n",
    "                  'other_current_assets float,' \\\n",
    "                  'total_current_assets float,' \\\n",
    "                  'loan_and_advance float,' \\\n",
    "                  'hold_for_sale_assets float,' \\\n",
    "                  'hold_to_maturity_investments float,' \\\n",
    "                  'longterm_receivable_account float,' \\\n",
    "                  'longterm_equity_invest float,' \\\n",
    "                  'investment_property float,' \\\n",
    "                  'fixed_assets float,' \\\n",
    "                  'constru_in_process float,' \\\n",
    "                  'construction_materials float,' \\\n",
    "                  'fixed_assets_liquidation float,' \\\n",
    "                  'biological_assets float,' \\\n",
    "                  'oil_gas_assets float,' \\\n",
    "                  'intangible_assets float,' \\\n",
    "                  'development_expenditure float,' \\\n",
    "                  'good_will float,' \\\n",
    "                  'long_deferred_expense float,' \\\n",
    "                  'deferred_tax_assets float,' \\\n",
    "                  'other_non_current_assets float,' \\\n",
    "                  'total_non_current_assets float,' \\\n",
    "                  'total_assets float,' \\\n",
    "                  'shortterm_loan float,' \\\n",
    "                  'borrowing_from_centralbank float,' \\\n",
    "                  'deposit_in_interbank float,' \\\n",
    "                  'borrowing_capital float,' \\\n",
    "                  'trading_liability float,' \\\n",
    "                  'notes_payable float,' \\\n",
    "                  'accounts_payable float,' \\\n",
    "                  'advance_peceipts float,' \\\n",
    "                  'sold_buyback_secu_proceeds float,' \\\n",
    "                  'commission_payable float,' \\\n",
    "                  'salaries_payable float,' \\\n",
    "                  'taxs_payable float,' \\\n",
    "                  'interest_payable float,' \\\n",
    "                  'dividend_payable float,' \\\n",
    "                  'other_payable float,' \\\n",
    "                  'reinsurance_payables float,' \\\n",
    "                  'insurance_contract_reserves float,' \\\n",
    "                  'proxy_secu_proceeds float,' \\\n",
    "                  'receivings_from_vicariously_sold_securities float,' \\\n",
    "                  'non_current_liability_in_one_year float,' \\\n",
    "                  'other_current_liability float,' \\\n",
    "                  'total_current_liability float,' \\\n",
    "                  'longterm_loan float,' \\\n",
    "                  'bonds_payable float,' \\\n",
    "                  'longterm_account_payable float,' \\\n",
    "                  'specific_account_payable float,' \\\n",
    "                  'estimate_liability float,' \\\n",
    "                  'deferred_tax_liability float,' \\\n",
    "                  'other_non_current_liability float,' \\\n",
    "                  'total_non_current_liability float,' \\\n",
    "                  'total_liability float,' \\\n",
    "                  'paidin_capital float,' \\\n",
    "                  'capital_reserve_fund float,' \\\n",
    "                  'treasury_stock float,' \\\n",
    "                  'specific_reserves float,' \\\n",
    "                  'surplus_reserve_fund float,' \\\n",
    "                  'ordinary_risk_reserve_fund float,' \\\n",
    "                  'retained_profit float,' \\\n",
    "                  'foreign_currency_report_conv_diff float,' \\\n",
    "                  'equities_parent_company_owners float,' \\\n",
    "                  'minority_interests float,' \\\n",
    "                  'total_owner_equities float,' \\\n",
    "                  'total_sheet_owner_equities float,' \\\n",
    "                  'constraint c2 foreign key (code_id) references code(id) on delete cascade );'\n",
    "    sql_cash_flow = 'create table cash_flow(' \\\n",
    "                    'id int primary key auto_increment,' \\\n",
    "                    'code_id int ,' \\\n",
    "                    'pubdate date ,' \\\n",
    "                    'statdate date ,' \\\n",
    "                    'goods_sale_and_service_render_cash float,' \\\n",
    "                    'net_deposit_increase float,' \\\n",
    "                    'net_borrowing_from_central_bank float,' \\\n",
    "                    'net_borrowing_from_finance_co float,' \\\n",
    "                    'net_original_insurance_cash float,' \\\n",
    "                    'net_cash_received_from_reinsurance_business float,' \\\n",
    "                    'net_insurer_deposit_investment float,' \\\n",
    "                    'net_deal_trading_assets float,' \\\n",
    "                    'interest_and_commission_cashin float,' \\\n",
    "                    'net_increase_in_placements float,' \\\n",
    "                    'net_buyback float,' \\\n",
    "                    'tax_levy_refund float,' \\\n",
    "                    'other_cashin_related_operate float,' \\\n",
    "                    'subtotal_operate_cash_inflow float,' \\\n",
    "                    'goods_and_services_cash_paid float,' \\\n",
    "                    'net_loan_and_advance_increase float,' \\\n",
    "                    'net_deposit_in_cb_and_ib float,' \\\n",
    "                    'original_compensation_paid float,' \\\n",
    "                    'handling_charges_and_commission float,' \\\n",
    "                    'policy_dividend_cash_paid float,' \\\n",
    "                    'staff_behalf_paid float,' \\\n",
    "                    'tax_payments float,' \\\n",
    "                    'other_operate_cash_paid float,' \\\n",
    "                    'subtotal_operate_cash_outflow float,' \\\n",
    "                    'net_operate_cash_flow float,' \\\n",
    "                    'invest_withdrawal_cash float,' \\\n",
    "                    'invest_proceeds float,' \\\n",
    "                    'fix_intan_other_asset_dispo_cash float,' \\\n",
    "                    'net_cash_deal_subcompany float,' \\\n",
    "                    'other_cash_from_invest_act float,' \\\n",
    "                    'subtotal_invest_cash_inflow float,' \\\n",
    "                    'fix_intan_other_asset_acqui_cash float,' \\\n",
    "                    'invest_cash_paid float,' \\\n",
    "                    'impawned_loan_net_increase float,' \\\n",
    "                    'net_cash_from_sub_company float,' \\\n",
    "                    'other_cash_to_invest_act float,' \\\n",
    "                    'subtotal_invest_cash_outflow float,' \\\n",
    "                    'net_invest_cash_flow float,' \\\n",
    "                    'cash_from_invest float,' \\\n",
    "                    'cash_from_mino_s_invest_sub float,' \\\n",
    "                    'cash_from_borrowing float,' \\\n",
    "                    'cash_from_bonds_issue float,' \\\n",
    "                    'other_finance_act_cash float,' \\\n",
    "                    'subtotal_finance_cash_inflow float,' \\\n",
    "                    'borrowing_repayment float,' \\\n",
    "                    'dividend_interest_payment float,' \\\n",
    "                    'proceeds_from_sub_to_mino_s float,' \\\n",
    "                    'other_finance_act_payment float,' \\\n",
    "                    'subtotal_finance_cash_outflow float,' \\\n",
    "                    'net_finance_cash_flow float,' \\\n",
    "                    'exchange_rate_change_effect float,' \\\n",
    "                    'cash_equivalent_increase float,' \\\n",
    "                    'cash_equivalents_at_beginning float,' \\\n",
    "                    'cash_and_equivalents_at_end float,' \\\n",
    "                    'constraint c3 foreign key (code_id) references code(id) on delete cascade );'\n",
    "    sql_income = 'create table income(' \\\n",
    "                 'id int primary key auto_increment,' \\\n",
    "                 'code_id int,' \\\n",
    "                 'pubdate date ,' \\\n",
    "                 'statdate date ,' \\\n",
    "                 'total_operating_revenue float,' \\\n",
    "                 'operating_revenue float,' \\\n",
    "                 'interest_income float,' \\\n",
    "                 'premiums_earned float,' \\\n",
    "                 'commission_income float,' \\\n",
    "                 'total_operating_cost float,' \\\n",
    "                 'operating_cost float,' \\\n",
    "                 'interest_expense float,' \\\n",
    "                 'commission_expense float,' \\\n",
    "                 'refunded_premiums float,' \\\n",
    "                 'net_pay_insurance_claims float,' \\\n",
    "                 'withdraw_insurance_contract_reserve float,' \\\n",
    "                 'policy_dividend_payout float,' \\\n",
    "                 'reinsurance_cost float,' \\\n",
    "                 'operating_tax_surcharges float,' \\\n",
    "                 'sale_expense float,' \\\n",
    "                 'administration_expense float,' \\\n",
    "                 'financial_expense float,' \\\n",
    "                 'asset_impairment_loss float,' \\\n",
    "                 'fair_value_variable_income float,' \\\n",
    "                 'investment_income float,' \\\n",
    "                 'invest_income_associates float,' \\\n",
    "                 'exchange_income float,' \\\n",
    "                 'operating_profit float,' \\\n",
    "                 'non_operating_revenue float,' \\\n",
    "                 'non_operating_expense float,' \\\n",
    "                 'disposal_loss_non_current_liability float,' \\\n",
    "                 'total_profit float,' \\\n",
    "                 'income_tax_expense float,' \\\n",
    "                 'net_profit float,' \\\n",
    "                 'np_parent_company_owners float,' \\\n",
    "                 'minority_profit float,' \\\n",
    "                 'basic_eps float,' \\\n",
    "                 'diluted_eps float,' \\\n",
    "                 'other_composite_income float,' \\\n",
    "                 'total_composite_income float,' \\\n",
    "                 'ci_parent_company_owners float,' \\\n",
    "                 'ci_minority_owners float,' \\\n",
    "                 'constraint c4 foreign key (code_id) references code(id) on delete cascade );'\n",
    "    sql_indicator = 'create table indicator(' \\\n",
    "                    'id int primary key auto_increment,' \\\n",
    "                    'code_id int,' \\\n",
    "                    'pubdate date ,' \\\n",
    "                    'statdate date ,' \\\n",
    "                    'eps float,' \\\n",
    "                    'adjusted_profit float,' \\\n",
    "                    'operating_profit_1 float,' \\\n",
    "                    'value_change_profit float,' \\\n",
    "                    'roe float,' \\\n",
    "                    'inc_return float,' \\\n",
    "                    'roa float,' \\\n",
    "                    'net_profit_margin float,' \\\n",
    "                    'gross_profit_margin float,' \\\n",
    "                    'expense_to_total_revenue float,' \\\n",
    "                    'operation_profit_to_total_revenue float,' \\\n",
    "                    'net_profit_to_total_revenue float,' \\\n",
    "                    'operating_expense_to_total_revenue float,' \\\n",
    "                    'ga_expense_to_total_revenue float,' \\\n",
    "                    'financing_expense_to_total_revenue float,' \\\n",
    "                    'operating_profit_to_profit float,' \\\n",
    "                    'invesment_profit_to_profit float,' \\\n",
    "                    'adjusted_profit_to_profit float,' \\\n",
    "                    'goods_sale_and_service_to_revenue float,' \\\n",
    "                    'ocf_to_revenue float,' \\\n",
    "                    'ocf_to_operating_profit float,' \\\n",
    "                    'inc_total_revenue_year_on_year float,' \\\n",
    "                    'inc_total_revenue_annual float,' \\\n",
    "                    'inc_revenue_year_on_year float,' \\\n",
    "                    'inc_revenue_annual float,' \\\n",
    "                    'inc_operation_profit_year_on_year float,' \\\n",
    "                    'inc_operation_profit_annual float,' \\\n",
    "                    'inc_net_profit_year_on_year float,' \\\n",
    "                    'inc_net_profit_annual float,' \\\n",
    "                    'inc_net_profit_to_shareholders_year_on_year float,' \\\n",
    "                    'inc_net_profit_to_shareholders_annual float,' \\\n",
    "                    'constraint c5 foreign key (code_id) references code(id) on delete cascade );'\n",
    "    # 光标实例化\n",
    "    cs = conn.cursor()\n",
    "    # 向MySQL发送sql语句 但不执行\n",
    "    cs.execute(sql_code)\n",
    "    cs.execute(sql_valuation)\n",
    "    cs.execute(sql_balance)\n",
    "    cs.execute(sql_cash_flow)\n",
    "    cs.execute(sql_income)\n",
    "    cs.execute(sql_indicator)\n",
    "    # 写入数据库\n",
    "    conn.commit()\n",
    "    cs.close()\n",
    "    t1 = time.time()\n",
    "    print('表格创建成功, 用时{}秒'.format(round(t1 - t0, 1)))\n",
    "\n",
    "\n",
    "def data_input(conn):\n",
    "    t0 = time.time()\n",
    "    df = pd.read_csv('{}/financial_data.csv'.format(run_dir), index_col=0)\n",
    "    # 数据处理\n",
    "    drop_columns = ['id', 'id.1', 'id.2', 'id.3', 'id.4',\n",
    "                    'code.1', 'code.2', 'code.3', 'code.4',\n",
    "                    'pubDate', 'pubDate.2', 'pubDate.3', 'pubDate.4',\n",
    "                    'statDate.1', 'statDate.2', 'statDate.3', 'statDate.4',\n",
    "                    'statDate.5', 'statDate.6', 'statDate.7']\n",
    "    df.drop(columns=drop_columns, inplace=True)\n",
    "    df.rename(columns={'pubDate.1': 'pubdate', 'statDate': 'statdate', 'operating_profit.1': 'operating_profit_1'}, inplace=True)\n",
    "    t1 = time.time()\n",
    "    print('提取数据完成，用时{}秒'.format(round(t1 - t0, 1)))\n",
    "    # 导入数据\n",
    "    df_code = pd.DataFrame(df['code'].unique(), columns=['code'])\n",
    "    df_code.to_sql(\n",
    "        'code',\n",
    "        conn,\n",
    "        index=False,\n",
    "        if_exists='append',\n",
    "    )\n",
    "    t2 = time.time()\n",
    "    print('code表数据导入成功, 用时{}秒'.format(round(t2 - t1, 1)))\n",
    "    # 创建code字典\n",
    "    code_id = pd.read_sql(\n",
    "        'select * from code',\n",
    "        conn,\n",
    "    )\n",
    "    code_id.index = code_id['code'].values\n",
    "    code_dict = code_id.to_dict()['id']\n",
    "    # valuation表数据导入\n",
    "    col_valuation = ['code', 'day', 'capitalization', 'market_cap', 'circulating_cap', 'circulating_market_cap',\n",
    "                     'turnover_ratio', 'pe_ratio', 'pe_ratio_lyr', 'pb_ratio', 'ps_ratio', 'pcf_ratio']\n",
    "    df_valuation = df[col_valuation]\n",
    "    df_valuation['code'] = df_valuation['code'].apply(lambda x: code_dict[x])\n",
    "    df_valuation.rename(columns={'code': 'code_id'}, inplace=True)\n",
    "    df_valuation.to_sql(\n",
    "        'valuation',\n",
    "        conn,\n",
    "        index=False,\n",
    "        if_exists='append',\n",
    "    )\n",
    "    t3 = time.time()\n",
    "    print('valuation表数据导入成功, 用时{}秒'.format(round(t3 - t2, 1)))\n",
    "    # balance表数据导入\n",
    "    col_balance = ['code', 'pubdate', 'statdate', 'cash_equivalents', 'settlement_provi', 'lend_capital', 'trading_assets', 'bill_receivable', 'account_receivable', 'advance_payment', 'insurance_receivables', 'reinsurance_receivables',\n",
    "                   'reinsurance_contract_reserves_receivable', 'interest_receivable', 'dividend_receivable', 'other_receivable', 'bought_sellback_assets', 'inventories', 'non_current_asset_in_one_year', 'other_current_assets', 'total_current_assets',\n",
    "                   'loan_and_advance', 'hold_for_sale_assets', 'hold_to_maturity_investments', 'longterm_receivable_account', 'longterm_equity_invest', 'investment_property', 'fixed_assets', 'constru_in_process', 'construction_materials',\n",
    "                   'fixed_assets_liquidation', 'biological_assets', 'oil_gas_assets', 'intangible_assets', 'development_expenditure', 'good_will', 'long_deferred_expense', 'deferred_tax_assets', 'other_non_current_assets',\n",
    "                   'total_non_current_assets', 'total_assets', 'shortterm_loan', 'borrowing_from_centralbank', 'deposit_in_interbank', 'borrowing_capital', 'trading_liability', 'notes_payable', 'accounts_payable', 'advance_peceipts',\n",
    "                   'sold_buyback_secu_proceeds', 'commission_payable', 'salaries_payable', 'taxs_payable', 'interest_payable', 'dividend_payable', 'other_payable', 'reinsurance_payables', 'insurance_contract_reserves', 'proxy_secu_proceeds',\n",
    "                   'receivings_from_vicariously_sold_securities', 'non_current_liability_in_one_year', 'other_current_liability', 'total_current_liability', 'longterm_loan', 'bonds_payable', 'longterm_account_payable', 'specific_account_payable',\n",
    "                   'estimate_liability', 'deferred_tax_liability', 'other_non_current_liability', 'total_non_current_liability', 'total_liability', 'paidin_capital', 'capital_reserve_fund', 'treasury_stock', 'specific_reserves',\n",
    "                   'surplus_reserve_fund', 'ordinary_risk_reserve_fund', 'retained_profit', 'foreign_currency_report_conv_diff', 'equities_parent_company_owners', 'minority_interests', 'total_owner_equities', 'total_sheet_owner_equities']\n",
    "    df_balance = df[col_balance]\n",
    "    df_balance['code'] = df_balance['code'].apply(lambda x: code_dict[x])\n",
    "    df_balance.rename(columns={'code': 'code_id'}, inplace=True)\n",
    "    df_balance.to_sql(\n",
    "        'balance',\n",
    "        conn,\n",
    "        index=False,\n",
    "        if_exists='append',\n",
    "    )\n",
    "    t4 = time.time()\n",
    "    print('balance表数据导入成功, 用时{}秒'.format(round(t4 - t3, 1)))\n",
    "    # cash_flow表数据导入\n",
    "    col_cash_flow = ['code', 'pubdate', 'statdate', 'goods_sale_and_service_render_cash', 'net_deposit_increase', 'net_borrowing_from_central_bank', 'net_borrowing_from_finance_co', 'net_original_insurance_cash', 'net_cash_received_from_reinsurance_business',\n",
    "                     'net_insurer_deposit_investment', 'net_deal_trading_assets', 'interest_and_commission_cashin', 'net_increase_in_placements', 'net_buyback', 'tax_levy_refund', 'other_cashin_related_operate', 'subtotal_operate_cash_inflow',\n",
    "                     'goods_and_services_cash_paid', 'net_loan_and_advance_increase', 'net_deposit_in_cb_and_ib', 'original_compensation_paid', 'handling_charges_and_commission', 'policy_dividend_cash_paid', 'staff_behalf_paid', 'tax_payments',\n",
    "                     'other_operate_cash_paid', 'subtotal_operate_cash_outflow', 'net_operate_cash_flow', 'invest_withdrawal_cash', 'invest_proceeds', 'fix_intan_other_asset_dispo_cash', 'net_cash_deal_subcompany', 'other_cash_from_invest_act',\n",
    "                     'subtotal_invest_cash_inflow', 'fix_intan_other_asset_acqui_cash', 'invest_cash_paid', 'impawned_loan_net_increase', 'net_cash_from_sub_company', 'other_cash_to_invest_act', 'subtotal_invest_cash_outflow',\n",
    "                     'net_invest_cash_flow', 'cash_from_invest', 'cash_from_mino_s_invest_sub', 'cash_from_borrowing', 'cash_from_bonds_issue', 'other_finance_act_cash', 'subtotal_finance_cash_inflow', 'borrowing_repayment', 'dividend_interest_payment',\n",
    "                     'proceeds_from_sub_to_mino_s', 'other_finance_act_payment', 'subtotal_finance_cash_outflow', 'net_finance_cash_flow', 'exchange_rate_change_effect', 'cash_equivalent_increase', 'cash_equivalents_at_beginning', 'cash_and_equivalents_at_end']\n",
    "    df_cash_flow = df[col_cash_flow]\n",
    "    df_cash_flow['code'] = df_cash_flow['code'].apply(lambda x: code_dict[x])\n",
    "    df_cash_flow.rename(columns={'code': 'code_id'}, inplace=True)\n",
    "    df_cash_flow.to_sql(\n",
    "        'cash_flow',\n",
    "        conn,\n",
    "        index=False,\n",
    "        if_exists='append',\n",
    "    )\n",
    "    t5 = time.time()\n",
    "    print('cash_flow表数据导入成功, 用时{}秒'.format(round(t5 - t4, 1)))\n",
    "    # income表数据导入\n",
    "    col_income = ['code', 'pubdate', 'statdate', 'total_operating_revenue', 'operating_revenue', 'interest_income', 'premiums_earned', 'commission_income', 'total_operating_cost', 'operating_cost', 'interest_expense', 'commission_expense', 'refunded_premiums',\n",
    "                  'net_pay_insurance_claims', 'withdraw_insurance_contract_reserve', 'policy_dividend_payout', 'reinsurance_cost', 'operating_tax_surcharges', 'sale_expense', 'administration_expense', 'financial_expense', 'asset_impairment_loss',\n",
    "                  'fair_value_variable_income', 'investment_income', 'invest_income_associates', 'exchange_income', 'operating_profit', 'non_operating_revenue', 'non_operating_expense', 'disposal_loss_non_current_liability', 'total_profit', 'income_tax_expense',\n",
    "                  'net_profit', 'np_parent_company_owners', 'minority_profit', 'basic_eps', 'diluted_eps', 'other_composite_income', 'total_composite_income', 'ci_parent_company_owners', 'ci_minority_owners']\n",
    "    df_income = df[col_income]\n",
    "    df_income['code'] = df_income['code'].apply(lambda x: code_dict[x])\n",
    "    df_income.rename(columns={'code': 'code_id'}, inplace=True)\n",
    "    df_income.to_sql(\n",
    "        'income',\n",
    "        conn,\n",
    "        index=False,\n",
    "        if_exists='append',\n",
    "    )\n",
    "    t6 = time.time()\n",
    "    print('income表数据导入成功, 用时{}秒'.format(round(t6 - t5, 1)))\n",
    "    # indicator表数据导入\n",
    "    col_indicator = ['code', 'pubdate', 'statdate', 'eps', 'adjusted_profit', 'operating_profit_1', 'value_change_profit', 'roe', 'inc_return', 'roa', 'net_profit_margin', 'gross_profit_margin', 'expense_to_total_revenue', 'operation_profit_to_total_revenue',\n",
    "                     'net_profit_to_total_revenue', 'operating_expense_to_total_revenue', 'ga_expense_to_total_revenue', 'financing_expense_to_total_revenue', 'operating_profit_to_profit', 'invesment_profit_to_profit', 'adjusted_profit_to_profit',\n",
    "                     'goods_sale_and_service_to_revenue', 'ocf_to_revenue', 'ocf_to_operating_profit', 'inc_total_revenue_year_on_year', 'inc_total_revenue_annual', 'inc_revenue_year_on_year', 'inc_revenue_annual', 'inc_operation_profit_year_on_year',\n",
    "                     'inc_operation_profit_annual', 'inc_net_profit_year_on_year', 'inc_net_profit_annual', 'inc_net_profit_to_shareholders_year_on_year', 'inc_net_profit_to_shareholders_annual']\n",
    "    df_indicator = df[col_indicator]\n",
    "    df_indicator['code'] = df_indicator['code'].apply(lambda x: code_dict[x])\n",
    "    df_indicator.rename(columns={'code': 'code_id'}, inplace=True)\n",
    "    df_indicator.to_sql(\n",
    "        'indicator',\n",
    "        conn,\n",
    "        index=False,\n",
    "        if_exists='append',\n",
    "    )\n",
    "    t7 = time.time()\n",
    "    print('indicator表数据导入成功, 用时{}秒'.format(round(t7 - t6, 1)))\n",
    "    print('数据导入本地数据库完成，总用时{}秒'.format(round(t7 - t0, 1)))\n",
    "\n",
    "    \n",
    "# python连接\n",
    "conn_py = connect_method(method='python')\n",
    "# pandas连接\n",
    "conn_pd = connect_method(method='pandas')\n",
    "print('创建表格...')\n",
    "creat_table(conn=conn_py)\n",
    "print('数据导入本地数据库中...')\n",
    "data_input(conn=conn_pd)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 导出.sql文件"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "  * mysqldump -h localhost -u root -p financial > E:\\financial.sql"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "MarkDown菜单",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
